
if exists (select 1 from sysobjects where name = 'get_dienstlisting')
   begin
     drop Procedure get_dienstlisting
     print 'Procedure: get_dienstlisting deleted ...'
   end
go

create procedure get_dienstlisting 
(
		@mandid     char(2) = 'li',
		@dienstid   int     = 0
)
as
begin

	select Anzeigename = d.Name + ' - ' + b.Kurztext,
	       DienstID    = d.DienstID,
	       Name        = d.Name,
	       Kuzrtext    = b.Kurztext,
	       RGB         = RGB
	  from Dienst d
	 left outer join Dienstplan dp
	    on dp.DienstID = d.DienstID
	 inner join Beschreibung b
	    on b.BeschrID = d.BeschrID
	 where d.mandid = @mandid
	   and (d.DienstID = @dienstid or @dienstid = 0)
	   and d.Status = 0
	 group by d.DienstID, d.Name, b.Kurztext, RGB
	 order by count(dp.DienstID) desc


end
go

print 'Procedure: get_dienstlisting done ...'
go
grant exec on get_dienstlisting to prsadmins with grant option
go
grant exec on get_dienstlisting to prsusers
go

